package vn.allmobile.ambnews.data;

import java.util.ArrayList;
import java.util.List;

import vn.allmobile.ambnews.utils.GlobalApplication;
import vn.allmobile.ambnews.utils.Log;
import vn.allmobile.ambnews.utils.PreferenceManager;
import vn.allmobile.ambnews.utils.Utils;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbFeedAdapter {

	
    
    /******************************  CHANELS ************************/
    
    public static long getLastSyncTime(String ppId,String catId)
    {
    		Cursor cursor = null;
    		long lastSyncTime = 0L;
    		//cursor = mDb.query(DbSchema.ChanelsSchema.TABLE_NAME,null,null,null,null,null,null);
    				cursor = GlobalApplication.getDB().query(DbSchema.ChanelsSchema.TABLE_NAME, null, DbSchema.ChanelsSchema.COLUMN_PAPER + 
    				"=? AND " + DbSchema.ChanelsSchema.COLUMN_CATEGORY +
    				"=?" , new String[]{ppId,catId}, null, null,null);
    	
    	
    	
    	boolean hasItem = cursor.moveToFirst();
    	if(hasItem)
    	{
    		while (cursor.isAfterLast() == false) {
    			lastSyncTime = cursor.getLong(cursor.getColumnIndex(DbSchema.ChanelsSchema.COLUMN_LASTUPDATE));
    			cursor.moveToNext();
    		}
    	}
    	if (cursor != null)
			cursor.close();    	
    	return lastSyncTime;
    }
    
    public static void updateLastSync(String ppId,String catId)
    {
    	
    	long lastSycn = Utils.currentMillis()/1000L;
    	String updateSQL = "INSERT OR REPLACE INTO " + DbSchema.ChanelsSchema.TABLE_NAME + " (" + DbSchema.ChanelsSchema.COLUMN_PAPER +", "  + DbSchema.ChanelsSchema.COLUMN_CATEGORY + ", "  + DbSchema.ChanelsSchema.COLUMN_LASTUPDATE + ")  VALUES ( '" + ppId + "', '" + catId + "',COALESCE((SELECT " + DbSchema.ChanelsSchema.COLUMN_LASTUPDATE +" FROM " + DbSchema.ChanelsSchema.TABLE_NAME + " WHERE pp_id = '" + ppId +"' AND cat_id = '" + catId+ "'), " + lastSycn + "));";
    	GlobalApplication.getDB().execSQL(updateSQL);
    	
    }
    
    
    
    /****************************************************************/
    
    public static boolean deleteOldAritlce()
    {
    	boolean rs = true;
    	try{
    		String deleteSQL = "DELETE FROM " + DbSchema.ArticleSchema.TABLE_NAME + " WHERE `date` <=  date('now','-30 day');";
        	GlobalApplication.getDB().execSQL(deleteSQL);
    	}catch(SQLException e){
    		rs = false;
    	}
    	return rs;
    }
    
    
    public static List<Article> getListArticle(String pp_id,String cat_id,boolean ishot) {
    	List<Article> articles = new ArrayList<Article>();
    	int limit = PreferenceManager.getArticleLimit();
    	Cursor cursor ;
    	if(ishot)
    	{
    		cursor 	= GlobalApplication.getDB().query(DbSchema.ArticleSchema.TABLE_NAME, null, DbSchema.ArticleSchema.COLUMN_TYPE +" = ?" , new String[]{"1"}, null, null, DbSchema.ArticleSchema.COLUMN_DATE + DbSchema.SORT_DESC + " LIMIT 0, " + limit );
    	}
    	else
    	{
    		cursor = GlobalApplication.getDB().query(DbSchema.ArticleSchema.TABLE_NAME, null, DbSchema.ArticleSchema.COLUMN_PAPER + "=? AND " + DbSchema.ArticleSchema.COLUMN_CATEGORY + "=?" , new String[]{pp_id,cat_id}, null, null, DbSchema.ArticleSchema.COLUMN_DATE + DbSchema.SORT_DESC + " LIMIT 0, " + limit );	
    	}
    	
    	
    	
    	try {
    		boolean hasItem = cursor.moveToFirst(); 
        	if(hasItem)
        	{
        		while (cursor.isAfterLast() == false) {
                    String id = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ID));
                    
                    String title = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_TITLE));
                  
                    String description = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DESCRIPTION));
                    
                    String url = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_URL));
                    
                    String ppid = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_PAPER));
                    String catid = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CATEGORY));
                    String image = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE));
                    
                    String content = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CONTENT));
                    
                    Integer img_height = cursor.getInt(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_HEIGHT));
                    
                    Integer img_width = cursor.getInt(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_WIDTH));
                    
                    String pubdate = cursor.getString(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DATE));
                    Integer isread = cursor.getInt(cursor
                            .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ISREAD));
                    Article article = new Article(id, title, description, pubdate, image, ppid, catid);
                    article.setHeight(String.valueOf(img_height));
                    article.setWidth(String.valueOf(img_width));
                    article.setContent(content);
                    article.setUrl(url);
                    article.setIsread(Boolean.valueOf(String.valueOf(isread)));
                    articles.add(article);	
                    cursor.moveToNext();
                }
        	}
        	if (cursor != null)
    			cursor.close();
    	} finally {
    	    cursor.close();
    	}
    	
    
    	return articles;
    }
    
    
    public static ContentValues getContentValueFromArticle(Article article)
    {
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_ID,article.getId());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_TITLE,article.getTitle());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_TYPE,String.valueOf(article.getType()));
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_DESCRIPTION,article.getDescription());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_PAPER,article.getPaperId());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_CATEGORY,article.getCatId());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_URL,article.getUrl());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_IMAGE,article.getImage());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_IMAGE_WIDTH,article.getWidth());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_IMAGE_HEIGHT,article.getHeight());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_DATE,article.getPubdate());
    	initialValues.put(DbSchema.ArticleSchema.COLUMN_CONTENT,article.getContent());
		return initialValues;    	
    	
    }
    public static Article getArticle(String articleid) {
    	Article article = null;    	
		Cursor cursor = GlobalApplication.getDB().query(DbSchema.ArticleSchema.TABLE_NAME, null ,DbSchema.ArticleSchema.COLUMN_ID + "= ?" , new String[]{articleid}, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String id = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ID));
	            
	            String title = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_TITLE));
	            
	            String description = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DESCRIPTION));
	            
	            String url = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_URL));
	            
	            String ppid = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_PAPER));
	            String catid = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CATEGORY));
	            String image = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE));
	            
	            String content = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CONTENT));
	            
	            Integer img_height = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_HEIGHT));
	            
	            Integer img_width = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_WIDTH));
	            
	            String pubdate = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DATE));
	            Integer isread = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ISREAD));
	            article = new Article(id, title, description, pubdate, image, ppid, catid);
	            article.setHeight(String.valueOf(img_height));
	            article.setWidth(String.valueOf(img_width));
	            article.setContent(content);
	            article.setUrl(url);
	            article.setIsread(Boolean.valueOf(String.valueOf(isread)));
				cursor.moveToNext();
			}
			
			if (cursor != null)
				cursor.close();
		} finally {
		    cursor.close();
		}
		
		
		return article;
    }
    
    public static  Article getLatestHotArticle()
    {
    	Article article = null;    	
		Cursor cursor = GlobalApplication.getDB().query(DbSchema.ArticleSchema.TABLE_NAME, null ,DbSchema.ArticleSchema.COLUMN_TYPE + "= ?" , new String[]{"1"}, null, null, DbSchema.ArticleSchema.COLUMN_DATE + DbSchema.SORT_DESC + " LIMIT 0,1");
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String id = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ID));
            
            String title = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_TITLE));
            
            String description = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DESCRIPTION));
            
            String url = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_URL));
            
            String ppid = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_PAPER));
            String catid = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CATEGORY));
            String image = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE));
            
            String content = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CONTENT));
            
            Integer img_height = cursor.getInt(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_HEIGHT));
            
            Integer img_width = cursor.getInt(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_WIDTH));
            
            String pubdate = cursor.getString(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DATE));
            Integer isread = cursor.getInt(cursor
                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ISREAD));
            article = new Article(id, title, description, pubdate, image, ppid, catid);
            article.setHeight(String.valueOf(img_height));
            article.setWidth(String.valueOf(img_width));
            article.setContent(content);
            article.setUrl(url);
            article.setIsread(Boolean.valueOf(String.valueOf(isread)));
			cursor.moveToNext();
		}
		
		if (cursor != null)
			cursor.close();
		
		return article;
    	
    }
    
    public static Article getLatestSyncArticle()
    {
    	Article article = null;    	
		Cursor cursor = GlobalApplication.getDB().query(DbSchema.ArticleSchema.TABLE_NAME, null ,"`type`= ?" , new String[]{"2"}, null, null, DbSchema.ArticleSchema.COLUMN_DATE + DbSchema.SORT_DESC + " LIMIT 0,1");		
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String id = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ID));
	            
	            String title = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_TITLE));
	            
	            String description = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DESCRIPTION));
	            
	            String url = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_URL));
	            
	            String ppid = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_PAPER));
	            String catid = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CATEGORY));
	            String image = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE));
	            
	            String content = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_CONTENT));
	            
	            Integer img_height = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_HEIGHT));
	            
	            Integer img_width = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_IMAGE_WIDTH));
	            
	            String pubdate = cursor.getString(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_DATE));
	            Integer isread = cursor.getInt(cursor
	                    .getColumnIndex(DbSchema.ArticleSchema.COLUMN_ISREAD));
	            article = new Article(id, title, description, pubdate, image, ppid, catid);
	            article.setHeight(String.valueOf(img_height));
	            article.setWidth(String.valueOf(img_width));
	            article.setContent(content);
	            article.setUrl(url);
	            article.setIsread(Boolean.valueOf(String.valueOf(isread)));
				cursor.moveToNext();
			}
			
			if (cursor != null)
				cursor.close();
		}catch(Exception e){e.printStackTrace();}
		finally {
		    cursor.close();
		}
		
		
		return article;
    }
    
    public static void addHotArticles(List<Article> articles)
    {
    	for(Article article : articles)
    	{
    		addOrUpdateArticle(article);
    	}
    }
    
    public static void addOrUpdateArticle(Article article)
    {
    	String sql = "INSERT OR REPLACE INTO " + DbSchema.ArticleSchema.TABLE_NAME + " VALUES ('" + article.getId() 
    			+ "'," + DatabaseUtils.sqlEscapeString(article.getTitle()) 
    			+ "," + DatabaseUtils.sqlEscapeString(article.getDescription()) 
    			+ ",'" + article.getPaperId() 
    			+ "','" + article.getCatId() 
    			+ "','" + article.getUrl() 
    			+ "'," + DatabaseUtils.sqlEscapeString(article.getContent()) 
    			+ ",'" + article.getImage() + "','" + article.getWidth() 
    			+ "','" + article.getHeight() + "','" + article.getType() 
    			+ "',0,'" + article.getPubdate() + "');";
    	GlobalApplication.getDB().execSQL(sql); 	
    }
    
    
    public static void addArticle(Article article)
    {
    	ContentValues initialValues = getContentValueFromArticle(article);
    	if(initialValues != null)
    	{
    		GlobalApplication.getDB().insertWithOnConflict(DbSchema.ArticleSchema.TABLE_NAME,null,initialValues,SQLiteDatabase.CONFLICT_IGNORE);
    	}    	
    }
    
    public static void addListAticle(List<Article> articles)
    {
    	for(Article article : articles)
    	{
    		addArticle(article);
    	}
    }
   /* public List<Article> getListArticle(String pp_id,String cat_id )
    {
    	
    	
    }
    */
    
    public static long addListArticle(ContentValues values, List<Article> items) {
    	
        long feedId = 0;
		return feedId;
    }
}
